package dao;

import modle.AthleteSignUpRecord;
import modle.JDBCUtil;
import modle.RefereeCompetitionInformation;

import java.sql.*;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

public class RefereeCompetitionInformationDao {
    private Connection connection = null;
    public List<RefereeCompetitionInformation> getRefereeCompetitionInformation(int r_id,int si_id, int ci_id, int g_id){
        List<RefereeCompetitionInformation> list = new ArrayList<RefereeCompetitionInformation>();
        PreparedStatement pstmt = null;//声明一个PreparedStatement对象并将其初始化为null
        StringBuilder sql = new StringBuilder("select * from v_referee_item_information WHERE 1=1");//参数占位符
        if(r_id != -100)
            sql.append(" AND R_ID = ?");
        if(si_id != -100)
            sql.append(" AND SI_ID = ?");
        if(ci_id != -100)
            sql.append(" AND CI_ID = ?");
        if(g_id != -100)
            sql.append(" AND G_ID = ?");
        sql.append(" ORDER BY CI_TIME ASC");//升序排序

        ResultSet rs = null;
        try{
            connection = JDBCUtil.getConn();
            pstmt = connection.prepareStatement(sql.toString(),ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            //ResultSet.TYPE_SCROLL_INSENSITIVE：游标可以前后移动，结果集对创建结果集后其他人对数据库所做的更改不敏感
            // 结果集是只读的，这是默认的并发类型
            int index = 1;
            if(r_id != -100){
                pstmt.setInt(index, r_id);
                index++;
            }
            if(si_id != -100){
                pstmt.setInt(index, si_id);
                index++;
            }
            if(ci_id != -100){
                pstmt.setInt(index, ci_id);
                index++;
            }
            if(g_id != -100){
                pstmt.setInt(index, g_id);
                index++;
            }

            rs = pstmt.executeQuery();
            int num = 1;//序号
            while (rs.next()) {
                Calendar cal = Calendar.getInstance();
                // 获取时间戳信息
                Timestamp rtimestamp = rs.getTimestamp("CI_Time");
                Timestamp rnewTimestamp = null;

                cal.setTimeInMillis(rtimestamp.getTime());
                // 对Calendar对象进行处理，减少8个小时
                cal.add(Calendar.HOUR_OF_DAY, -8);

                // 使用Calendar对象创建一个新的timestamp对象
                rnewTimestamp = new java.sql.Timestamp(cal.getTime().getTime());

                RefereeCompetitionInformation refereeCompetitionInformation = new RefereeCompetitionInformation();
                refereeCompetitionInformation.setId(rs.getInt("RC_ID"));
                refereeCompetitionInformation.setNum(num++);
                refereeCompetitionInformation.setCi_id(rs.getInt("CI_ID"));
                refereeCompetitionInformation.setR_id(rs.getInt("R_ID"));
                refereeCompetitionInformation.setR_name(rs.getString("R_NAME"));
                refereeCompetitionInformation.setStartTime(rnewTimestamp);
                refereeCompetitionInformation.setVenue(rs.getString("CI_Venue"));
                refereeCompetitionInformation.setSi_id(rs.getInt("SI_ID"));
                refereeCompetitionInformation.setSi_name(rs.getString("SI_Name"));
                refereeCompetitionInformation.setIntroduction(rs.getString("SI_Introduction"));
                refereeCompetitionInformation.setGroupName(rs.getString("G_NAME"));
                refereeCompetitionInformation.setNature(rs.getString("CI_Nature"));
                refereeCompetitionInformation.setIssue(rs.getInt("CI_Issue"));
                list.add(refereeCompetitionInformation);
            }
            pstmt.close();
            connection.close();
        }catch (SQLException e) {
            //e.printStackTrace();
        }
        catch (NullPointerException e){
            return null;
        }finally{
            try{
                if (connection != null && (!connection.isClosed())){
                    connection.close();
                }
            }catch(SQLException e){
                //e.printStackTrace();
            }
        }
        return list;
    }
}
